﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;
using System.Collections.Generic;

namespace DMZS.DAL
{
    /// <summary>
    /// 数据访问类:InStock
    /// </summary>
    public partial class InStock : Dlyndx
    {
        public InStock()
        {
        }

        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public long AddExpand(DMZS.Model.InStock model)
        {
            var list_sql = new List<string>();
            var list_parm = new List<SqlParameter[]>();

            #region 添加表头
            var strSql_head = @"
--单据类型
if not exists(select count(1) from Vchtype where vchtype=@VchType)
begin
    select -10021
    return
end
--供应商 
if @btypeid<>0 and ( not exists(select count(1) from Supplier where ID=@btypeid) )
begin
    select -10022
    return
end

--部门
if not exists(select count(1) from DepartmentNew where ID=@deptid)
begin
    select -10023
    return
end

insert into Dlyndx(
    Date,Number,VchType,Comment,btypeid,etypeid,ifcheck,checke,checkeDate,InputNo,draft,DeptID,isScan,SaleMan,SalePurpose
)
values (
    @Date,@Number,@VchType,@Comment,@btypeid,@etypeid,@ifcheck,@checke,@checkeDate,@InputNo,@draft,@DeptID,@isScan,@SaleMan,@SalePurpose
);

if object_id('tempdb..##tempTable') is not null begin
    drop table ##tempTable
end
create table ##tempTable(ID bigint)

insert into ##tempTable(ID) values(@@IDENTITY )
select ID from ##tempTable 
";
            var parameters_head = new SqlParameter[] { new SqlParameter("@Date", SqlDbType.DateTime),
                    new SqlParameter("@Number", SqlDbType.VarChar, 40),
                    new SqlParameter("@VchType", SqlDbType.Int, 4),
                    new SqlParameter("@Comment", SqlDbType.VarChar, 256),
                    new SqlParameter("@btypeid", SqlDbType.VarChar, 25),
                    new SqlParameter("@etypeid", SqlDbType.VarChar, 25),
                    new SqlParameter("@ifcheck", SqlDbType.Int, 4),
                    new SqlParameter("@checke", SqlDbType.VarChar, 25),
                    new SqlParameter("@checkeDate", SqlDbType.DateTime),
                    new SqlParameter("@InputNo", SqlDbType.VarChar, 25),
                    new SqlParameter("@draft", SqlDbType.Int, 4),
                    new SqlParameter("@DeptID", SqlDbType.VarChar, 25),
                    new SqlParameter("@isScan", SqlDbType.Int, 4),
                    new SqlParameter("@SaleMan", SqlDbType.VarChar, 25),
                    new SqlParameter("@SalePurpose", SqlDbType.VarChar, 200) };
            parameters_head[0].Value = model.Date;
            parameters_head[1].Value = model.Number;
            parameters_head[2].Value = model.VchType;
            parameters_head[3].Value = model.Comment;
            parameters_head[4].Value = model.btypeid;
            parameters_head[5].Value = model.etypeid;
            parameters_head[6].Value = model.ifcheck;
            parameters_head[7].Value = model.checke;
            parameters_head[8].Value = model.checkeDate;
            parameters_head[9].Value = model.InputNo;
            parameters_head[10].Value = model.draft;
            parameters_head[11].Value = model.DeptID;
            parameters_head[12].Value = model.isScan;
            parameters_head[13].Value = model.SaleMan;
            parameters_head[14].Value = model.SalePurpose;

            list_sql.Add(strSql_head);
            list_parm.Add(parameters_head);
            #endregion

            #region 添加表体
            foreach (DataRow row in model.Tbody.Rows)
            {
                var strSql_body = @"
--产品
if not exists(select count(1) from Product where Code=@PtypeId)
begin
    select 1    --跳过
    return
end

declare @Vchcode bigint
select @Vchcode=ID from ##tempTable 

insert into DlyStorage(Vchcode,ktypeid,ptypeid,Qty,price,total,date,OrderType,OrderCode,OrderDlyCode,DeptID,PStutas,unit,Comment,unit1,qty1) 
values(@Vchcode,@ktypeid,@ptypeid,@Qty,@price,@total,@date,@OrderType,@OrderCode,@OrderDlyCode,@DeptID,@PStutas,@unit,@Comment,@unit1,@qty1)
select @@identity
";

                var parameters_body = new SqlParameter[] { 
                    new SqlParameter("@Vchcode1", SqlDbType.Decimal, 5),
                    new SqlParameter("@ktypeid", SqlDbType.VarChar, 25),
                    new SqlParameter("@ptypeid", SqlDbType.VarChar, 100),
                    new SqlParameter("@Qty", SqlDbType.Decimal, 9),
                    new SqlParameter("@price", SqlDbType.Decimal, 9),
                    new SqlParameter("@total", SqlDbType.Decimal, 9),
                    new SqlParameter("@date", SqlDbType.DateTime),
                    new SqlParameter("@OrderType", SqlDbType.Int, 4),
                    new SqlParameter("@OrderCode", SqlDbType.Int, 4),
                    new SqlParameter("@OrderDlyCode", SqlDbType.Int, 4),
                    new SqlParameter("@DeptID", SqlDbType.VarChar, 25),
                    new SqlParameter("@PStutas", SqlDbType.Int, 4),
                    new SqlParameter("@unit", SqlDbType.Int, 4),
                    new SqlParameter("@Comment", SqlDbType.VarChar, 256),
                    new SqlParameter("@unit1", SqlDbType.Int, 4),
                    new SqlParameter("@qty1", SqlDbType.Decimal, 9) };
                parameters_body[0].Value = row["Vchcode"];
                parameters_body[1].Value = row["WarehouseID"];
                parameters_body[2].Value = row["ProductID"];
                parameters_body[3].Value = row["Qty"];
                parameters_body[4].Value = row["Price"];
                parameters_body[5].Value = row["total"];
                parameters_body[6].Value = row["date"];
                parameters_body[7].Value = row["OrderType"];
                parameters_body[8].Value = row["OrderCode"];
                parameters_body[9].Value = row["OrderDlyCode"];
                parameters_body[10].Value = row["DeptID"];
                parameters_body[11].Value = row["PStutas"];
                parameters_body[12].Value = row["unit"];
                parameters_body[13].Value = row["CommentSub"];
                parameters_body[14].Value = row["unit1"];
                parameters_body[15].Value = row["qty1"];

                list_sql.Add(strSql_body);
                list_parm.Add(parameters_body);
            }
            #endregion

            #region 添加条码
            foreach (DataRow row in model.Tbarcode.Rows)
            {
                var strSql_body = @"
declare @Vchcode bigint
select @Vchcode=ID from ##tempTable 
--检查 待做

--

declare @OrderLineID bigint
select @OrderLineID=DlyOrder from DlyStorage where Vchcode=@Vchcode and ptypeid=@ProductID

insert into BarcodeOperationLog(BarcodeID,NewStatus,OldStatus,Time,OrderTypeID,OrderID,OrderLineID)
select @BarcodeID,10,Status,getdate(),@OrderTypeID,@Vchcode,@OrderLineID from Barcode where ID=@BarcodeID

update Barcode set Status=10 where ID=@BarcodeID

insert into BarcodeStorageOperation(BarcodeID,ProductID,OrderTypeID,OrderID,OrderLineID) 
values(@BarcodeID,@ProductID,@OrderTypeID,@Vchcode,@OrderLineID)
select @@identity
";

                var parameters_body = new SqlParameter[] { 
                    new SqlParameter("@BarcodeID", SqlDbType.BigInt, 8),
                    new SqlParameter("@ProductID", SqlDbType.BigInt, 8),
                    new SqlParameter("@OrderTypeID", SqlDbType.Int, 4) };
                parameters_body[0].Value = row["ID"];
                parameters_body[1].Value = row["ProductID"];
                parameters_body[2].Value = row["OrderTypeID"];

                list_sql.Add(strSql_body);
                list_parm.Add(parameters_body);
            }
            #endregion

            #region 删除临时表
            var strSql_end = @"
declare @ID bigint
select @ID=ID from ##tempTable
drop table ##tempTable
select @ID";
            list_sql.Add(strSql_end);
            list_parm.Add(null);
            #endregion

            var result = string.Empty;
            DbHelperSQL.ExecuteSqlTran(list_sql, list_parm, ref result);
            return Convert.ToInt64(result);

        }

        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public long UpdateExpand(DMZS.Model.InStock model)
        {
            var list_sql = new List<string>();
            var list_parm = new List<SqlParameter[]>();

            #region 更新表头
            var strSql_head = @"
--单据类型
if not exists(select count(1) from Vchtype where vchtype=@VchType)
begin
    select -10021
    return
end
--供应商 
if @btypeid<>0 and ( not exists(select count(1) from Supplier where ID=@btypeid) )
begin
    select -10022
    return
end

--部门
if not exists(select count(1) from DepartmentNew where ID=@deptid)
begin
    select -10023
    return
end

update Dlyndx
set
    --Date=@Date,
    --Number=@Number,
    --VchType=@VchType,
    Comment=@Comment,
    btypeid=@btypeid,
    etypeid=@etypeid,
    --ifcheck=@ifcheck,
    --checke=@checke,
    --checkeDate=@checkeDate,
    --draft=@draft,
    DeptID=@DeptID,
    isScan=@isScan
    --SaleMan=@SaleMan,
    --SalePurpose=@SalePurpose
where Vchcode=@Vchcode
select @Vchcode
";
            var parameters_head = new SqlParameter[] { new SqlParameter("@Date", SqlDbType.DateTime),
                    new SqlParameter("@Number", SqlDbType.VarChar, 40),
                    new SqlParameter("@VchType", SqlDbType.Int, 4),
                    new SqlParameter("@Comment", SqlDbType.VarChar, 256),
                    new SqlParameter("@btypeid", SqlDbType.VarChar, 25),
                    new SqlParameter("@etypeid", SqlDbType.VarChar, 25),
                    new SqlParameter("@ifcheck", SqlDbType.Int, 4),
                    new SqlParameter("@checke", SqlDbType.VarChar, 25),
                    new SqlParameter("@checkeDate", SqlDbType.DateTime),
                    new SqlParameter("@InputNo", SqlDbType.VarChar, 25),
                    new SqlParameter("@draft", SqlDbType.Int, 4),
                    new SqlParameter("@DeptID", SqlDbType.VarChar, 25),
                    new SqlParameter("@isScan", SqlDbType.Int, 4),
                    new SqlParameter("@SaleMan", SqlDbType.VarChar, 25),
                    new SqlParameter("@SalePurpose", SqlDbType.VarChar, 200),
                    new SqlParameter("@Vchcode", SqlDbType.BigInt,8)};
            parameters_head[0].Value = model.Date;
            parameters_head[1].Value = model.Number;
            parameters_head[2].Value = model.VchType;
            parameters_head[3].Value = model.Comment;
            parameters_head[4].Value = model.btypeid;
            parameters_head[5].Value = model.etypeid;
            parameters_head[6].Value = model.ifcheck;
            parameters_head[7].Value = model.checke;
            parameters_head[8].Value = model.checkeDate;
            parameters_head[9].Value = model.InputNo;
            parameters_head[10].Value = model.draft;
            parameters_head[11].Value = model.DeptID;
            parameters_head[12].Value = model.isScan;
            parameters_head[13].Value = model.SaleMan;
            parameters_head[14].Value = model.SalePurpose;
            parameters_head[15].Value = model.Vchcode;


            list_sql.Add(strSql_head);
            list_parm.Add(parameters_head);
            #endregion

            #region 删除条码移除项
            var str_barocdeid_now = ",0";
            foreach (DataRow row in model.Tbarcode.Rows)
            {
                if (row["BarcodeStorageID"] != DBNull.Value && row["BarcodeStorageID"].ToString() != string.Empty)
                {
                    str_barocdeid_now += "," + row["ID"].ToString();
                }
            }

            var strSql_barcode_delete = @"
--检查 待做

--

declare @id bigint
declare @productid bigint
declare cur_barcode cursor for
    select BarcodeID,ProductID from BarcodeStorageOperation 
    where OrderTypeID=@OrderTypeID and OrderID=@Vchcode
    and BarcodeID not in ({0})

open cur_barcode
fetch next from cur_barcode into @id,@productid
while @@fetch_status=0	
begin
    declare @status int 

    select top 1 @status=NewStatus 
    from BarcodeOperationLog 
    where BarcodeID=@ID and OrderTypeID<>@OrderTypeID and OrderID<>@Vchcode
    and OldStatus<>-1 and NewStatus<>-1 --创建或打印
    order by ID desc

    if @status is null begin set @status=1 end
    update Barcode set Status=@status where ID=@id

    declare @orderlineid bigint
    select @orderlineid=DlyOrder from DlyStorage where Vchcode=@Vchcode and ptypeid=@productid

    insert into BarcodeOperationLog(BarcodeID,NewStatus,OldStatus,Time,OrderTypeID,OrderID,OrderLineID)
    select @id,@status,20,getdate(),@OrderTypeID,@Vchcode,@orderlineid 

	fetch next from cur_barcode into @id,@productid
end
close cur_barcode
deallocate cur_barcode

delete BarcodeStorageOperation 
where OrderTypeID=@OrderTypeID and OrderID=@Vchcode
and BarcodeID not in ({0})

select 1 
";
            strSql_barcode_delete = string.Format(strSql_barcode_delete, str_barocdeid_now.Substring(1));
            var parameters_barcode_delete = new SqlParameter[] { 
                    new SqlParameter("@OrderTypeID", SqlDbType.Int),
                    new SqlParameter("@Vchcode", SqlDbType.BigInt)
                };
            parameters_barcode_delete[0].Value = model.VchType;
            parameters_barcode_delete[1].Value = model.Vchcode;

            list_sql.Add(strSql_barcode_delete);
            list_parm.Add(parameters_barcode_delete);
            #endregion

            #region 删除表体移除项
            var str_id_now = ",0";
            foreach (DataRow row in model.Tbody.Rows)
            {
                if (row["DlyOrder"] != DBNull.Value && row["DlyOrder"].ToString() != string.Empty)
                {
                    str_id_now += "," + row["DlyOrder"].ToString();
                }
            }

            var strSql_body_delete = @"
delete DlyStorage where Vchcode=@Vchcode and (DlyOrder not in({0}))
select 1 
";
            var parameters_body_delete = new SqlParameter[] { 
                    new SqlParameter("@Vchcode", SqlDbType.BigInt)
                };
            parameters_body_delete[0].Value = model.Vchcode;

            strSql_body_delete = string.Format(strSql_body_delete, str_id_now.Substring(1));

            list_sql.Add(strSql_body_delete);
            list_parm.Add(parameters_body_delete);
            #endregion

            #region 更新表体
            foreach (DataRow row in model.Tbody.Rows)
            {
                string strSql_body;
                if (row["DlyOrder"] == DBNull.Value || row["DlyOrder"].ToString() == string.Empty)
                {
                    strSql_body = @"
--产品
if not exists(select count(1) from Product where Code=@PtypeId)
begin
    select 1    --跳过
    return
end

insert into DlyStorage(Vchcode,ktypeid,ptypeid,Qty,price,total,date,OrderType,OrderCode,OrderDlyCode,DeptID,PStutas,unit,Comment,unit1,qty1) 
values(@Vchcode,@ktypeid,@ptypeid,@Qty,@price,@total,@date,@OrderType,@OrderCode,@OrderDlyCode,@DeptID,@PStutas,@unit,@Comment,@unit1,@qty1)
select @@identity
";
                }
                else
                {
                    strSql_body = @"
--产品
if not exists(select count(1) from Product where Code=@PtypeId)
begin
    select 1    --跳过
    return
end

update DlyStorage set
   --Vchcode=@Vchcode,
   ktypeid=@ktypeid,
   ptypeId=@ptypeId,
   Qty=@Qty,
   price=@price,
   --total=@total,
   --Date=@Date,
   unit=@unit,
   OrderType=@OrderType,
   OrderCode=@OrderCode,
   OrderDlyCode=@OrderDlyCode,
   Comment=@Comment
where DlyOrder=@DlyOrder
select @DlyOrder
";
                }

                var parameters_body = new SqlParameter[] { 
                    new SqlParameter("@Vchcode", SqlDbType.Decimal, 5),
                    new SqlParameter("@ktypeid", SqlDbType.VarChar, 25),
                    new SqlParameter("@ptypeid", SqlDbType.VarChar, 100),
                    new SqlParameter("@Qty", SqlDbType.Decimal, 9),
                    new SqlParameter("@price", SqlDbType.Decimal, 9),
                    new SqlParameter("@total", SqlDbType.Decimal, 9),
                    new SqlParameter("@date", SqlDbType.DateTime),
                    new SqlParameter("@OrderType", SqlDbType.Int, 4),
                    new SqlParameter("@OrderCode", SqlDbType.Int, 4),
                    new SqlParameter("@OrderDlyCode", SqlDbType.Int, 4),
                    new SqlParameter("@DeptID", SqlDbType.VarChar, 25),
                    new SqlParameter("@PStutas", SqlDbType.Int, 4),
                    new SqlParameter("@unit", SqlDbType.Int, 4),
                    new SqlParameter("@Comment", SqlDbType.VarChar, 256),
                    new SqlParameter("@unit1", SqlDbType.Int, 4),
                    new SqlParameter("@qty1", SqlDbType.Decimal, 9),
                    new SqlParameter("@DlyOrder", SqlDbType.Decimal,9)};
                parameters_body[0].Value = model.Vchcode;
                parameters_body[1].Value = row["WarehouseID"];
                parameters_body[2].Value = row["ProductID"];
                parameters_body[3].Value = row["Qty"];
                parameters_body[4].Value = row["Price"];
                parameters_body[5].Value = row["total"];
                parameters_body[6].Value = row["date"];
                parameters_body[7].Value = row["OrderType"];
                parameters_body[8].Value = row["OrderCode"];
                parameters_body[9].Value = row["OrderDlyCode"];
                parameters_body[10].Value = row["DeptID"];
                parameters_body[11].Value = row["PStutas"];
                parameters_body[12].Value = row["unit"];
                parameters_body[13].Value = row["CommentSub"];
                parameters_body[14].Value = row["unit1"];
                parameters_body[15].Value = row["qty1"];
                parameters_body[16].Value = row["DlyOrder"];

                list_sql.Add(strSql_body);
                list_parm.Add(parameters_body);
            }
            #endregion

            #region 更新条码
            foreach (DataRow row in model.Tbarcode.Rows)
            {
                if (row["BarcodeStorageID"] == DBNull.Value || row["BarcodeStorageID"].ToString() == string.Empty)
                {
                    string strSql_barcode = @"
--检查 待做

--

declare @OrderLineID bigint
select @OrderLineID=DlyOrder from DlyStorage where Vchcode=@Vchcode and ptypeid=@ProductID

insert into BarcodeOperationLog(BarcodeID,NewStatus,OldStatus,Time,OrderTypeID,OrderID,OrderLineID)
select @BarcodeID,10,Status,getdate(),@OrderTypeID,@Vchcode,@OrderLineID from Barcode where ID=@BarcodeID

update Barcode set Status=10 where ID=@BarcodeID

insert into BarcodeStorageOperation(BarcodeID,ProductID,OrderTypeID,OrderID,OrderLineID) 
values(@BarcodeID,@ProductID,@OrderTypeID,@Vchcode,@OrderLineID)
select @@identity
";

                    var parameters_barcode = new SqlParameter[] { 
                    new SqlParameter("@BarcodeID", SqlDbType.BigInt, 8),
                    new SqlParameter("@ProductID", SqlDbType.BigInt, 8),
                    new SqlParameter("@OrderTypeID", SqlDbType.Int, 4),
                    new SqlParameter("@Vchcode",SqlDbType.BigInt) };
                    parameters_barcode[0].Value = row["ID"];
                    parameters_barcode[1].Value = row["ProductID"];
                    parameters_barcode[2].Value = row["OrderTypeID"];
                    parameters_barcode[3].Value = model.Vchcode;

                    list_sql.Add(strSql_barcode);
                    list_parm.Add(parameters_barcode);
                }
            }
            #endregion

            var result = string.Empty;
            DbHelperSQL.ExecuteSqlTran(list_sql, list_parm, ref  result);
            return Convert.ToInt64(result);
        }


        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public int DeleteExpand(long ID, int OrderTypeID)
        {
            var strSql = @"
begin tran

declare @draft int
select @draft=draft from Dlyndx where Vchcode=@Vchcode
if @draft is null begin
    commit tran
    select 1    --已被删
    return
end

if @draft=1 begin
    commit tran
    select -10031   --已审核
    return
end

if @draft=2 begin
    commit tran
    select -10032   --已关闭
    return
end

declare @id bigint
declare @productid bigint
declare cur_barcode cursor for
    select BarcodeID,ProductID from BarcodeStorageOperation 
    where OrderTypeID=@OrderTypeID and OrderID=@Vchcode

open cur_barcode
fetch next from cur_barcode into @id,@productid
while @@fetch_status=0	
begin
    declare @status int 

    select top 1 @status=NewStatus 
    from BarcodeOperationLog 
    where BarcodeID=@ID and OrderTypeID<>@OrderTypeID and OrderID<>@Vchcode
    and OldStatus<>-1 and NewStatus<>-1 --创建或打印
    order by ID desc

    if @status is null begin set @status=1 end
    update Barcode set Status=@status where ID=@id

    declare @orderlineid bigint
    select @orderlineid=DlyOrder from DlyStorage where Vchcode=@Vchcode and ptypeid=@productid

    insert into BarcodeOperationLog(BarcodeID,NewStatus,OldStatus,Time,OrderTypeID,OrderID,OrderLineID)
    select @id,@status,10,getdate(),@OrderTypeID,@Vchcode,@orderlineid 

	fetch next from cur_barcode into @id,@productid
end
close cur_barcode
deallocate cur_barcode

delete BarcodeStorageOperation 
where OrderTypeID=@OrderTypeID and OrderID=@Vchcode

delete Dlyndx where Vchcode=@Vchcode
delete DlyStorage where Vchcode=@Vchcode

if @@error<>0 begin
    rollback tran
    select 0
end else begin
    commit tran
    select 1
end
";
            var parameters = new SqlParameter[] { 
                new SqlParameter("@Vchcode", SqlDbType.BigInt, 8),
                new SqlParameter("@OrderTypeID",SqlDbType.Int)};
            parameters[0].Value = ID;
            parameters[1].Value = OrderTypeID;

            var obj = DbHelperSQL.GetSingle(strSql, parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 审核
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="checke"></param>
        /// <param name="OrderTypeID"></param>
        /// <returns></returns>
        public int Check(long ID, string checke, int OrderTypeID)
        {
            var strSql = @"
declare @draft int
select @draft=draft from Dlyndx where Vchcode=@Vchcode

if @draft is null begin
    select -1000
    return
end

if @draft=1 begin
    select -10001
    return
end

if @draft=2 begin
    select -1003
    return
end


update Dlyndx set ifcheck=1, checke=@checke,draft=1 where Vchcode=@Vchcode

insert into BarcodeOperationLog(BarcodeID,NewStatus,OldStatus,Time,OrderTypeID,OrderID,OrderLineID)
select b.ID,11,Status,getdate(),bs.OrderTypeID,bs.OrderID,bs.OrderLineID 
from Barcode b
join BarcodeStorageOperation bs on bs.BarcodeID=b.ID
where OrderID=@Vchcode and OrderTypeID=@OrderTypeID

update Barcode set Status=11 
where ID in (select BarcodeID from BarcodeStorageOperation where OrderID=@Vchcode and OrderTypeID=@OrderTypeID) 
select @Vchcode
";
            var parameters = new SqlParameter[] { 
                    new SqlParameter("@checke", SqlDbType.VarChar, 25),
                    new SqlParameter("@Vchcode", SqlDbType.Decimal),
                    new SqlParameter("@OrderTypeID", SqlDbType.Int)};
            parameters[0].Value = checke;
            parameters[1].Value = ID;
            parameters[2].Value = OrderTypeID;

            var obj = DbHelperSQL.GetSingle(strSql, parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 反审核
        /// -1000:被删除
        /// -10002:未审核
        /// -1003:已关闭
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public virtual int UnCheck(long ID, int OrderTypeID)
        {
            var strSql = @"
declare @draft int
select @draft=draft from Dlyndx where Vchcode=@Vchcode

if @draft is null begin
    select -1000
    return
end

if @draft=0 begin
    select -10002
    return
end

if @draft=2 begin
    select -1003
    return
end

update Dlyndx set ifcheck=0, checke=@checke,draft=0 where Vchcode=@Vchcode

insert into BarcodeOperationLog(BarcodeID,NewStatus,OldStatus,Time,OrderTypeID,OrderID,OrderLineID)
select b.ID,10,Status,getdate(),bs.OrderTypeID,bs.OrderID,bs.OrderLineID 
from Barcode b
join BarcodeStorageOperation bs on bs.BarcodeID=b.ID
where OrderID=@Vchcode and OrderTypeID=@OrderTypeID

update Barcode set Status=10 
where ID in (select BarcodeID from BarcodeStorageOperation where OrderID=@Vchcode and OrderTypeID=@OrderTypeID) 
select @Vchcode
";
            var parameters = new SqlParameter[] { 
                    new SqlParameter("@checke", SqlDbType.VarChar, 25),
                    new SqlParameter("@Vchcode", SqlDbType.Decimal),
                    new SqlParameter("@OrderTypeID", SqlDbType.Int)};
            parameters[0].Value = null;
            parameters[1].Value = ID;
            parameters[2].Value = OrderTypeID;

            var obj = DbHelperSQL.GetSingle(strSql, parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 关闭
        /// -1000:被删除
        /// -10002:未审核
        /// -1003:已关闭
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public virtual int Close(long ID, int OrderTypeID)
        {
            var strSql = @"
declare @draft int
select @draft=draft from Dlyndx where Vchcode=@Vchcode

if @draft is null begin
    select -1000
    return
end

if @draft=0 begin
    select -10002
    return
end

if @draft=2 begin
    select -1003
    return
end

update Dlyndx set draft=2 where Vchcode=@Vchcode
select @Vchcode
";
            var parameters = new SqlParameter[] { new SqlParameter("@Vchcode", SqlDbType.Decimal) };
            parameters[0].Value = ID;

            var obj = DbHelperSQL.GetSingle(strSql, parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 获取表头
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public DataRow GetHead(long ID)
        {
            string strSql = @"select * from vInStock where Vchcode=@ID";
            var parameters = new SqlParameter[] { new SqlParameter("@ID", SqlDbType.BigInt, 8)
            };
            parameters[0].Value = ID;

            var ds = DbHelperSQL.Query(strSql, parameters);

            if (ds == null || ds.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            return ds.Tables[0].Rows[0];
        }

        /// <summary>
        /// 获取表体
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public virtual DataTable GetBody(long ID)
        {
            var strSql = @"select * from vInStockLines where Vchcode=@Vchcode";
            var parameters = new SqlParameter[] { new SqlParameter("@Vchcode", SqlDbType.BigInt, 8)
            };
            parameters[0].Value = ID;

            var ds = DbHelperSQL.Query(strSql, parameters);

            if (ds == null)
            {
                return null;
            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 获取数据 对话框
        /// </summary>
        /// <param name="code"></param>
        /// <param name="status">0:全部; 1:待审; 2:已审; 3:关闭</param>
        /// <param name="dateIndex">0:近七天; 1:本周; 2:近一月; 3:本月; 4:全部; 5:全部</param>
        public DataSet GetListForDialog(string code, int status, int dateIndex)
        {
            var strSql = @" 
select * from vInStock 
where Number like '%'+@code+'%' and {0} and {1}
order by Vchcode desc";

            var strDate = string.Empty;
            int intStatus;
            var date = DateTime.Now.Date;
            switch (dateIndex)
            {
                case 0:
                    strDate += "convert(nvarchar,date,112)>=convert(nvarchar, dateadd(day,-7,getdate()), 112)";
                    break;
                case 1:
                    var day = date.DayOfWeek;
                    var addDay = day == DayOfWeek.Sunday ? 6 : Convert.ToInt32(day) - 1;
                    strDate += "convert(nvarchar,date,112)>='" + string.Format("{0:yyyyMMdd}", date.AddDays(-1 * addDay)) + "'";
                    break;
                case 2:
                    strDate += "convert(nvarchar,date,112)>=convert(nvarchar, dateadd(day,-30,getdate()), 112)";
                    break;
                case 3:
                    strDate += "convert(nvarchar,date,112)>='" + string.Format("{0:yyyyMMdd}", date.AddDays(1 - date.Day)) + "'";

                    break;
                case 4:
                    strDate += "1=1";
                    break;
                default:
                    strDate += "1=1";
                    break;
            }
            switch (status)
            {
                case 0:
                    intStatus = -1;
                    break;
                case 1:
                    intStatus = 0;
                    break;
                case 2:
                    intStatus = 1;
                    break;
                case 3:
                    intStatus = 2;
                    break;
                default:
                    intStatus = -1;
                    break;
            }

            if (intStatus == -1)
            {
                strSql = string.Format(strSql, strDate, "1=1");
            }
            else
            {
                strSql = string.Format(strSql, strDate, "draft=@draft");
            }

            var parameters = new SqlParameter[] { new SqlParameter("@code", SqlDbType.VarChar, 40),
                    new SqlParameter("@draft", SqlDbType.Int, 4) };
            parameters[0].Value = code;
            parameters[1].Value = intStatus;

            return DbHelperSQL.Query(strSql, parameters);
        }
    }
}

